/**
 * 面板统计数据
 */
import db, { mysql } from 'src-ssr/db/connect';
import * as tools from 'src-ssr/util/common';
import shortid from 'shortid';
import { Sequelize, Op } from 'sequelize';
import models from 'src-ssr/db/sequelize';
import moment from 'moment';

// 面板统计数据
export const managerAbstract = async (req, res) => {
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  // 只能系统管理员才能取到数据
  if (user.tid > 0) {
    return res.json({
      liveness: [],
      flop: {
        tenant_total: 0,
        tenant_near: 0,
        tenant_expire: 0,
        user_total: 0,
        consumer_total: 0,
        spending: 0
      }
    });
  }
  // 活跃度前10的租户，按30天操作日志数量排行
  const [livenessRows] = await mysql.query(`SELECT t.tenant_name, l.liveness
    FROM m_tenants t
    INNER JOIN (
      SELECT tenant_id, COUNT(id) liveness
      FROM t_operation_logs
      WHERE tenant_id > 0 AND create_time>UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY))
      GROUP BY tenant_id
    ) l ON t.id=l.tenant_id
    ORDER BY l.liveness DESC
    LIMIT 10`);

  // 活跃度计算为：与第一名的占比（百分比）
  if (livenessRows.length > 0) {
    const first = livenessRows[0].liveness;
    livenessRows.forEach(row => {
      row.liveness = ~~(row.liveness / first * 100);
    });
  }
  const orilen = 10 - livenessRows.length;
  for (let i = 0; i < orilen; i++) {
    livenessRows.push({ tenant_name: '暂空', liveness: 0 });
  }

  // 租户数
  const [tenantRows] = await mysql.query(`SELECT COUNT(mt.id) total,
    SUM(IF(expire_time-UNIX_TIMESTAMP()<31*86400 AND expire_time-UNIX_TIMESTAMP()>0, 1, 0)) near,
    SUM(IF(expire_time-UNIX_TIMESTAMP()<0, 1, 0)) expire
    FROM m_tenants mt
    WHERE mt.id > 0`);

  // 用户数
  const [userRows] = await mysql.query(`SELECT COUNT(id) cnt
    FROM m_users
    WHERE tenant_id>0`);

  // 消费者总量
  const [consumerRows] = await mysql.query(`SELECT COUNT(id) cnt
    FROM m_consumers`);
  // 消费金额
  const [spendingRows] = await mysql.query(`SELECT IFNULL(SUM(money), 0) money
    FROM t_sale_details`);

  res.json({
    liveness: livenessRows,
    flop: {
      tenant_total: tenantRows[0].total, // 租户总数
      tenant_near: tenantRows[0].near, // 有效期剩余30天的租户数量
      tenant_expire: tenantRows[0].expire, // 有效期已过期的租户数量
      user_total: userRows[0].cnt, // 用户总数
      consumer_total: consumerRows[0].cnt, // 消费者总数
      spending: Number(spendingRows[0].money / 10000).toFixed(1) // 消费金额
    }
  });
};
